In a bid to address the issue, I fiddled a while with mysql's indexes.. To begin with I didn't have much success, but with a little bit of playing I started to realise what it's all about.
To create an index is easy. Let's say you have a table that looke like so:
create table shoes (
shoe_id int(5) auto_incement,
shoe_style int(2),
shoe_color text,
shoe_introduced datetime,
primary key(shoe_id)
);
An index on the style column is easily added with a statement such as:
alter table shoes add index shoe_style(shoe_style);
Now imagine this column is going to join to another column in a 'shirts' table - let's call it shirt_style, then you would probably want to create an index in the same way on shirts.shirt_style. This help to accelerate cross-table queries containing where clauses such as '.. from shoes, shirts where shoe_style=shirt_style and ..'
MySQL's EXPLAIN syntax is idea for checking on how your optimisation is going, or for forming an optimisation strategy in the first place. Watch your row counts on query explainations and use them as indication of how long the query might take to execute.
To see the indexes created on a table, use a statement such as:
show index from shoes;
you can use alter statements to remove indexes as in:
alter table shoes drop index shoe_style;
As an indication of the performance improvements that can be achieved using indexes on MyISAM tables, my 1 hour reporting script had its execution time reduced to just over 1 minute. This is a hugely significant time reduction, which massively reduces CPU payload on the database server, reduces lead time on script generation, test and debug and of course means a shorter execution time - handy for non-cronned jobs. I will be looking into using this method to speed up other Domain modification tools that I have in the system - cool!